package com.zyz.bookborrowing.dao;

import com.zyz.bookborrowing.bean.Book;
import com.zyz.bookborrowing.bean.PageBean;
import com.zyz.bookborrowing.utils.DelFlagE;
import com.zyz.bookborrowing.utils.MyDButils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl2 implements IBookDao2{

    String sql;
    QueryRunner queryRunner;

    @Override
    public List<Book> list(Book book) {
        queryRunner = MyDButils.getQueryRunner();
        sql = "select * from t_book where is_deleted = ?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<Book>>() {
                @Override
                public List<Book> handle(ResultSet rs) throws SQLException {
                    List<Book> list = new ArrayList<>();
                    while (rs.next()) {
                        Book b = new Book();
                        b.setId(rs.getInt("id"));
                        b.setBookName(rs.getString("book_name"));
                        b.setAuthor(rs.getString("author"));
                        b.setPublish(rs.getString("publish"));
                        b.setIsbn(rs.getString("isbn"));
                        String introduction = rs.getString("introduction");
                        if (introduction != null && !"".equals(introduction) && introduction.length() > 20){
                            introduction = rs.getString("introduction").substring(0, 20);
                        }
                        b.setIntroduction(introduction + "...");
                        b.setLanguage(rs.getString("language"));
                        b.setPrice(rs.getString("price"));
                        b.setPubdate(rs.getString("pubdate"));
                        b.setPressmark(rs.getString("pressmark"));
                        b.setState(rs.getInt("state"));
                        b.setIsDeleted(rs.getInt("is_deleted"));
                        list.add(b);
                    }
                    return list;
                }
            }, DelFlagE.NO.code);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Integer save(Book book) {
        queryRunner = MyDButils.getQueryRunner();
        sql = "insert into t_book(book_name, author, publish, isbn, introduction, language, price, pubdate, pressmark) values(?,?,?,?,?,?,?,?,?)";
        try {
            return queryRunner.update(sql, book.getBookName(), book.getAuthor(), book.getPublish(), book.getIsbn(), book.getIntroduction(), book.getLanguage(), book.getPrice(), book.getPubdate(), book.getPressmark());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

//    public static void main(String[] args) {
//        Book book = new Book();
//        book.setBookName("神秘的书");
//        book.setAuthor("laladlal");
//        book.setId(1);
//        BookDaoImpl dao = new BookDaoImpl();
////        dao.save(book);
//        List<Book> list = dao.list(null);
//        dao.updateBook(book);
//
//    }

    @Override
    public Integer updateBook(Book book) {
        queryRunner = MyDButils.getQueryRunner();
        if (book.getIsDeleted() == null || "".equals(book.getIsDeleted())){
            book.setIsDeleted(DelFlagE.NO.code);
        }
        if (book.getState() == null || "".equals(book.getState())){
            book.setState(0);
        }
        sql = "update t_book set book_name = ?, author = ?, publish = ?, isbn = ?, introduction = ?, language = ?, price = ?, pubdate = ?, pressmark = ?, state = ? where id = ?";
        try {
            return queryRunner.update(sql, book.getBookName(), book.getAuthor(), book.getPublish(), book.getIsbn(), book.getIntroduction(), book.getLanguage(), book.getPrice(), book.getPubdate(), book.getPressmark(), book.getState(), book.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return -1;
    }

    @Override
    public Integer deleteById(Integer id) {
        queryRunner = MyDButils.getQueryRunner();
        sql = "update t_book set is_deleted = ? where id = ?";
        try {
            return queryRunner.update(sql, DelFlagE.YES.code, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return -1;
    }

    @Override
    public Book queryById(Integer id) {
        queryRunner = MyDButils.getQueryRunner();
        sql = "select * from t_book where is_deleted = ? and id = ?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<Book>() {

                public Book handle(ResultSet rs) throws SQLException {
                    if (rs.next()) {
                        Book b = new Book();
                        b.setId(rs.getInt("id"));
                        b.setBookName(rs.getString("book_name"));
                        b.setAuthor(rs.getString("author"));
                        b.setPublish(rs.getString("publish"));
                        b.setIsbn(rs.getString("isbn"));
                        String introduction = rs.getString("introduction");
                        b.setIntroduction(introduction);
                        b.setLanguage(rs.getString("language"));
                        b.setPrice(rs.getString("price"));
                        b.setPubdate(rs.getString("pubdate"));
                        b.setPressmark(rs.getString("pressmark"));
                        b.setState(rs.getInt("state"));
                        b.setIsDeleted(rs.getInt("is_deleted"));
                        return b;
                    }
                    return null;
                }
            }, DelFlagE.NO.code, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Book> queryListByState(int code) {
        queryRunner = MyDButils.getQueryRunner();
        sql = "select * from t_book where is_deleted = ? and state = ?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<Book>>() {
                @Override
                public List<Book> handle(ResultSet rs) throws SQLException {
                    List<Book> list = new ArrayList<>();
                    while (rs.next()) {
                        Book b = new Book();
                        b.setId(rs.getInt("id"));
                        b.setBookName(rs.getString("book_name"));
                        b.setAuthor(rs.getString("author"));
                        b.setPublish(rs.getString("publish"));
                        b.setIsbn(rs.getString("isbn"));
                        String introduction = rs.getString("introduction");
                        if (introduction != null && !"".equals(introduction) && introduction.length() > 20){
                            introduction = rs.getString("introduction").substring(0, 20);
                        }
                        b.setIntroduction(introduction + "...");
                        b.setLanguage(rs.getString("language"));
                        b.setPrice(rs.getString("price"));
                        b.setPubdate(rs.getString("pubdate"));
                        b.setPressmark(rs.getString("pressmark"));
                        b.setState(rs.getInt("state"));
                        b.setIsDeleted(rs.getInt("is_deleted"));
                        list.add(b);
                    }
                    return list;
                }
            }, DelFlagE.NO.code, code);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public PageBean<Book> queryAll(String no) {
        PageBean<Book> pageBean = new PageBean<>();
        int count = new Long(getTotalCount()).intValue();
        pageBean.setTotalCount(count);
        int no1 = Integer.parseInt(no);
        if(no1 < 1)
            no1 = 1;
        else if(no1 > pageBean.getTotalPage())
            no1 = pageBean.getTotalPage();
        pageBean.setCurrentPage(no1);
        String sql = "select * from t_book where is_deleted = ? limit ?,? ";
        List<Book> list = new ArrayList<>();

        QueryRunner queryRunner = MyDButils.getQueryRunner();
        try {
            queryRunner.query(sql, new ResultSetHandler<List<Book>>() {
                @Override
                public List<Book> handle(ResultSet rs) throws SQLException {

                    while (rs.next()) {
                        Book b = new Book();
                        b.setId(rs.getInt("id"));
                        b.setBookName(rs.getString("book_name"));
                        b.setAuthor(rs.getString("author"));
                        b.setPublish(rs.getString("publish"));
                        b.setIsbn(rs.getString("isbn"));
                        String introduction = rs.getString("introduction");
                        if (introduction != null && !"".equals(introduction) && introduction.length() > 20){
                            introduction = rs.getString("introduction").substring(0, 20);
                        }
                        b.setIntroduction(introduction + "...");
                        b.setLanguage(rs.getString("language"));
                        b.setPrice(rs.getString("price"));
                        b.setPubdate(rs.getString("pubdate"));
                        b.setPressmark(rs.getString("pressmark"));
                        b.setState(rs.getInt("state"));
                        b.setIsDeleted(rs.getInt("is_deleted"));
                        list.add(b);
                    }
                    return list;
                }
            }, DelFlagE.NO.code, pageBean.getStartNum(), pageBean.getPageSize());
            pageBean.setPageData(list);
            return pageBean;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    @Override
    public long getTotalCount() {
        QueryRunner queryRunner = MyDButils.getQueryRunner();

        String sql = "select count(*) from t_book where is_deleted = ?";
        long count = 0;
        try {
            count = queryRunner.query(sql, new ScalarHandler<Long>(), DelFlagE.NO.code);
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
}
